-- ------------------------------------------------------------------------------------------- --
--                                Formel 1 Datenbank                                           --
-- ------------------------------------------------------------------------------------------- --
-- Prof. Susanne Busse
-- Mai 2010

-- Skript zum Erstellen der Datenbank (Sybase ASE)

-- Etwaiges Loeschen der Tabellen
if exists (select 1
            from  sysobjects
            where id = object_id('GefahrenVon')
            and   type = 'U')
   drop table GefahrenVon
go

if exists (select 1
            from  sysobjects
            where id = object_id('Rennen')
            and   type = 'U')
   drop table Rennen
go

if exists (select 1
            from  sysobjects
            where id = object_id('SetztEin')
            and   type = 'U')
   drop table SetztEin
go

if exists (select 1
            from  sysobjects
            where id = object_id('Rennfahrer')
            and   type = 'U')
   drop table Rennfahrer
go

if exists (select 1
            from  sysobjects
            where id = object_id('Rennstall')
            and   type = 'U')
   drop table Rennstall
go

if exists (select 1
            from  sysobjects
            where id = object_id('Saison')
            and   type = 'U')
   drop table Saison
go

if exists (select 1
            from  sysobjects
            where id = object_id('Rennstrecke')
            and   type = 'U')
   drop table Rennstrecke
go

if exists (select 1
            from  sysobjects
            where id = object_id('Land')
            and   type = 'U')
   drop table Land
go


-- Anlegen der Tabellen
create table Land (
  LandID      Numeric(8,0)  identity   primary key,
  Name        Varchar(40)   not null
  ,Flagge      image         null
)


go
create table Rennstrecke (
  StreckenID  Numeric(10,0) identity   primary key,
  Name        Varchar(40)   not null,
  LandID      Numeric(8,0)  not null   references Land
)


go

create table Saison (
  Jahr        Numeric(4,0)  not null   primary key
)


go
create table Rennstall (
  RennstallID    Numeric(8,0)   identity  primary key,
  Name           Varchar(40)    not null,
  Punkte         Numeric(7,2)   not null, -- abgeleitetes Attribut!
  Jahr           Numeric(4,0)   not null  references Saison,
)


go
create table Rennfahrer (
  RennfahrerID     Numeric(10,0)  identity   primary key,
  Name             Varchar(30)    not null,
  Vorname          Varchar(30)    not null,
  Geburtstag       Date           not null,
  Nationalitaet    Numeric(8,0)   not null   references Land,
  Siege            Numeric(4,0)   default 0  not null, -- abgeleitetes Attribut!
  PolePositions    Numeric(4,0)   default 0  not null, -- abgeleitetes Attribut!
  SchnellsteRunden Numeric(4,0)   default 0  not null, -- abgeleitetes Attribut!
  Punkte           Numeric(7,2)   default 0  not null -- abgeleitetes Attribut!
)


go
create table SetztEin (
  RennstallID    Numeric(8,0)    not null   references Rennstall,
  RennfahrerID   Numeric(10,0)   not null   references Rennfahrer,  
  Punkte         Numeric(6,2)    default 0  not null,  
  Primary Key (RennstallID, RennfahrerID)
)


go
create table Rennen (
  Datum              Date          not null   primary key,
  StreckenID         Numeric(10,0) not null   references Rennstrecke,
  Jahr               Numeric(4,0)  not null   references Saison,
  PoleFahrerID       Numeric(10,0) null       references Rennfahrer,
  SchnellsteRundeID  Numeric(10,0) null       references Rennfahrer
)


go
create table GefahrenVon (
  RennfahrerID    Numeric(10,0)   not null  references Rennfahrer,  
  Datum           Date            not null  references Rennen,
  Platz           Numeric(2,0)    null,
  primary key (RennfahrerID, Datum)
)


go
-- beim Aktualisieren eines Rennens sind die abgeleiteten Attribute (Poles und
-- SchnellsteRunden) der Rennfahrer ebenfalls anzupassen.
create trigger AdaptPolesUndSchnellsteRunden
on Rennen
for insert, update
as
begin
  if update (PoleFahrerID) 
  begin
    update Rennfahrer
     set PolePositions = PolePositions - 1
     from deleted d
     where d.PoleFahrerID = RennfahrerID
   
    update Rennfahrer
     set PolePositions = PolePositions + 1
     from inserted i
     where i.PoleFahrerID = RennfahrerID
  end

  if update (SchnellsteRundeID) 
  begin
    update Rennfahrer
     set SchnellsteRunden = SchnellsteRunden - 1
     from deleted d
     where d.SchnellsteRundeID = RennfahrerID
   
    update Rennfahrer
     set SchnellsteRunden = SchnellsteRunden + 1
     from inserted i
     where i.SchnellsteRundeID = RennfahrerID
  end
end
   
go

-- und beim Loeschen eines Rennens sind die Poles und Schnellste Runden
-- bei den jeweiligen Fahrern zu loeschen.
create trigger DeletePoleUndSchnellsteRunden
on Rennen
for delete
as
begin
  update Rennfahrer
   set PolePositions = PolePositions - 1
   from deleted d
   where d.PoleFahrerID = RennfahrerID

  update Rennfahrer
   set SchnellsteRunden = SchnellsteRunden - 1
   from deleted d
   where d.SchnellsteRundeID = RennfahrerID
end
 
go
-- beim Aktualisieren des Ergebnisses eines Rennfahrers bei einem Rennen
-- ist ggf. die Anzahl seiner Siege anzupassen.
create trigger AdaptSiege
on GefahrenVon
for insert,update
as begin
  update Rennfahrer
   set Siege = Siege - 1
   from deleted d
   where d.RennfahrerID = Rennfahrer.RennfahrerID
     and d.Platz = 1

  update Rennfahrer
   set Siege = Siege + 1
   from inserted i
   where i.RennfahrerID = Rennfahrer.RennfahrerID
     and i.Platz = 1
end

go
-- und beim Loeschen einer Teilnahme entsprechend
create trigger DeleteSieg
on GefahrenVon
for delete
as begin
  update Rennfahrer
   set Siege = Siege - 1
   from deleted d
   where d.RennfahrerID = Rennfahrer.RennfahrerID
     and d.Platz = 1
end

go
-- beim Eintragen des Einsatzes eines Fahrers fuer ein Rennteam mit
-- seinen Punkten, muessen die Punkte des Fahrers und des Rennstalls
-- entsprechend angepasst werden.
create trigger AdaptPunkte
on SetztEin
for insert, update
as begin
  -- Abziehen der Punkte bei veralteten Daten
  update Rennfahrer
   set Punkte = Rennfahrer.Punkte - d.Punkte
   from deleted d
   where d.RennfahrerID = Rennfahrer.RennfahrerID

  update Rennstall
   set Punkte = Rennstall.Punkte - d.Punkte
   from deleted d
   where d.RennstallID = Rennstall.RennstallID

  -- Hinzufuegen der Punkte der aktualisierten Datensaetze
  update Rennfahrer
   set Punkte = Rennfahrer.Punkte + i.Punkte
   from inserted i
   where i.RennfahrerID = Rennfahrer.RennfahrerID

  update Rennstall
   set Punkte = Rennstall.Punkte + i.Punkte
   from inserted i
   where i.RennstallID = Rennstall.RennstallID
end

go
-- und Loeschen wieder entsprechend
create trigger DeletePunkte
on SetztEin
for delete
as begin
  update Rennfahrer
   set Punkte = Rennfahrer.Punkte - d.Punkte
   from deleted d
   where d.RennfahrerID = Rennfahrer.RennfahrerID

  update Rennstall
   set Punkte = Rennstall.Punkte - d.Punkte
   from deleted d
   where d.RennstallID = Rennstall.RennstallID
end
